#coding=utf-8
import MySQLdb
import uuid
from  datetime  import  *
from collections import namedtuple



conn= MySQLdb.connect(
        host='192.168.145.44',
        port = 5506,
        user='root',
        passwd='mysql',
        db ='contract_product-2017-9-9',
        charset="utf8"
        )
cur = conn.cursor()

#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")


reCount = cur.execute("select * from yx_contract")
# result = cur.fetchall()


#修改查询条件的数据
# cur.execute("update base_car_brand set logo='3 year 1 class' where ID = '2c95802b55b592240155b5aebb3b0eb1'")

#删除查询条件的数据
#cur.execute("delete from student where age='9'")

def generate_namedtuple(cur):
    from collections import namedtuple
    fieldnames = [d[0].lower() for d in cur.description]
    Record = namedtuple('Record', fieldnames)
    rows = cur.fetchall()
    if not rows:return
    else:
        return map(Record._make, rows)

for k in generate_namedtuple(cur):
        origin_id = k.id
        trace_id = uuid.uuid1()
        trace_id = str(trace_id).replace("-","")

        apply_id = uuid.uuid1()
        apply_id = str(apply_id).replace("-","")

        todo_task_id  = uuid.uuid1()
        todo_task_id = str(todo_task_id).replace("-","")

        # print(k.bill_number, k.bill_type,k.checker)
        # cur.execute("insert into student(ID,CREATE_TIME,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATE_TIME,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION,approve_date,business_line,contract_amount,contract_code,contract_info,contract_status,contract_type,erp_project,execute_end_date,execute_start_date,is_normal,is_onfile,is_relcontract,on_file_date,rel_contract_code,signed_area,signed_company,signed_date,signed_dept,signed_dept_id,signed_user,signed_user_id,stamp_date,valid_date,final_channel_id,more_channel_id,channel_id,contract_modle_id,income_sure_type,income_type,pre_contract_id,new_contract_id,sys_date,sys_status,is_return,BZ_ID,contract_division_code,contract_division_name,file_status,master_slave_contract,other_seal_status,person_charge,person_charge_domain_account,seal_status,submitter_domain_account,submitter_name,final_channel_code,more_channel_code,channel_code,signed_company_code,final_channel_name,more_channel_name,channel_name,master_slave_contract_code,application_amount,remark,person_charge_dept,person_charge_dept_id,seal_dept,seal_dept_id,seal_domain_account,seal_name,contract_apply_id,income_sure_type_code,income_type_code,weather_frameagree,project_name,weather_settlement,relay_contract_code,relay_contract_id,master_slave_contract_id,final_channel_number,more_channel_number,channel_number,has_import_tcData,start_time_tc,end_time_tc,import_tc_data_time) "
        #             " values(%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s)",
        #             (k.id,k.create_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.create_time is None)   else '',
        #              k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,
        #              k.update_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.update_time is None)   else '',
        #              k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,
        #              k.approve_date.strftime("%Y-%m-%d %H:%M:%S")  if not (k.approve_date is None)   else '',
        #              k.business_line,k.contract_amount,k.contract_code,k.contract_info,k.contract_status,k.contract_type,k.erp_project,
        #              k.execute_end_date.strftime("%Y-%m-%d %H:%M:%S") if not (k.execute_end_date is None)   else '',
        #              k.execute_start_date.strftime("%Y-%m-%d %H:%M:%S") if not (k.execute_start_date is None)   else '',
        #              k.is_normal,k.is_onfile,k.is_relcontract,k.on_file_date,k.rel_contract_code,k.signed_area,k.signed_company,
        #              k.signed_date.strftime("%Y-%m-%d %H:%M:%S") if not (k.signed_date is None)   else '',
        #              k.signed_dept,k.signed_dept_id,k.signed_user,k.signed_user_id,
        #              k.stamp_date.strftime("%Y-%m-%d %H:%M:%S") if not (k.stamp_date is None)   else '',
        #              k.valid_date,k.final_channel_id,k.more_channel_id,k.channel_id,k.contract_modle_id,
        #              k.income_sure_type,k.income_type,k.pre_contract_id,k.new_contract_id,k.sys_date,k.sys_status,k.is_return,k.bz_id,k.contract_division_code,k.contract_division_name,k.file_status,k.master_slave_contract,
        #              k.other_seal_status,k.person_charge,k.person_charge_domain_account,k.seal_status,k.submitter_domain_account,k.submitter_name,k.final_channel_code,k.more_channel_code,k.channel_code,k.signed_company_code,
        #              k.final_channel_name,k.more_channel_name,k.channel_name,k.master_slave_contract_code,k.application_amount,k.remark,k.person_charge_dept,k.person_charge_dept_id,k.seal_dept,k.seal_dept_id,
        #              k.seal_domain_account,k.seal_name,k.contract_apply_id,k.income_sure_type_code,k.income_type_code,k.weather_frameagree,k.project_name,k.weather_settlement,k.relay_contract_code,k.relay_contract_id,k.master_slave_contract_id,k.final_channel_number,k.more_channel_number,k.channel_number,k.has_import_tcdata,k.start_time_tc,k.end_time_tc,
        #              k.import_tc_data_time))
        # cur.execute("insert into student(ID,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION,business_line,contract_amount,contract_code,contract_info,contract_status,contract_type,erp_project,is_normal,is_onfile,is_relcontract,on_file_date,rel_contract_code,signed_area,signed_company,signed_dept,signed_dept_id,signed_user,signed_user_id,valid_date,final_channel_id,more_channel_id,channel_id,contract_modle_id,income_sure_type,income_type,pre_contract_id,new_contract_id,sys_date,sys_status,is_return,BZ_ID,contract_division_code,contract_division_name,file_status,master_slave_contract,other_seal_status,person_charge,person_charge_domain_account,seal_status,submitter_domain_account,submitter_name,final_channel_code,more_channel_code,channel_code,signed_company_code,final_channel_name,more_channel_name,channel_name,master_slave_contract_code,application_amount,remark,person_charge_dept,person_charge_dept_id,seal_dept,seal_dept_id,seal_domain_account,seal_name,contract_apply_id,income_sure_type_code,income_type_code,weather_frameagree,project_name,weather_settlement,relay_contract_code,relay_contract_id,master_slave_contract_id,final_channel_number,more_channel_number,channel_number,has_import_tcData,start_time_tc,end_time_tc,import_tc_data_time) values(%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,$s,%s)",(k.id,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,k.business_line,k.contract_amount,k.contract_code,k.contract_info,k.contract_status,k.contract_type,k.erp_project,k.is_normal,k.is_onfile,k.is_relcontract,k.on_file_date,k.rel_contract_code,k.signed_area,k.signed_company,k.signed_dept,k.signed_dept_id,k.signed_user,k.signed_user_id,k.valid_date,k.final_channel_id,k.more_channel_id,k.channel_id,k.contract_modle_id,k.income_sure_type,k.income_type,k.pre_contract_id,k.new_contract_id,k.sys_date,k.sys_status,k.is_return,k.bz_id,k.contract_division_code,k.contract_division_name,k.file_status,k.master_slave_contract,k.other_seal_status,k.person_charge,k.person_charge_domain_account,k.seal_status,k.submitter_domain_account,k.submitter_name,k.final_channel_code,k.more_channel_code,k.channel_code,k.signed_company_code,k.final_channel_name,k.more_channel_name,k.channel_name,k.master_slave_contract_code,k.application_amount,k.remark,k.person_charge_dept,k.person_charge_dept_id,k.seal_dept,k.seal_dept_id,k.seal_domain_account,k.seal_name,k.contract_apply_id,k.income_sure_type_code,k.income_type_code,k.weather_frameagree,k.project_name,k.weather_settlement,k.relay_contract_code,k.relay_contract_id,k.master_slave_contract_id,k.final_channel_number,k.more_channel_number,k.channel_number,k.has_import_tcdata,k.start_time_tc,k.end_time_tc,k.import_tc_data_time))
        # cur.execute("insert into yx_contract_trace(ID,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION)"
        #             " values(%s,$s,%s,$s,$s,%s,$s,$s,%s,$s,%s)",(k.id,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version))
        # now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        #   新增表（yx_contract_trace）数据
        cur.execute(
                "insert into yx_contract_trace(ID,CREATE_TIME,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATE_TIME,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,version,"
                " approve_date,business_line,contract_amount,contract_code,contract_info,contract_status,contract_type,erp_project,execute_end_date,execute_start_date,is_normal,is_onfile,is_relcontract, "
                " valid_date,final_channel_id,more_channel_id,channel_id,contract_model_id,income_sure_type,income_type,pre_contract_id,new_contract_id,sys_date,"
                " sys_status,is_return,BZ_ID,file_status,master_slave_contract,other_seal_status,person_charge,person_charge_domain_account,"
                " seal_status,submitter_domain_account,submitter_name,signed_company_code,final_channel_name,more_channel_name,channel_name,"
                " master_slave_contract_code,application_amount,remark,seal_dept,seal_dept_id,seal_domain_account,seal_name, "
                " income_sure_type_code,income_type_code,weather_frameagree,weather_settlement,relay_contract_code,relay_contract_id,master_slave_contract_id,final_channel_number,more_channel_number, "
                " channel_number,"
                
                "contract_model_name)"
                #                                ID,CREATE_TIME,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATE_TIME,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION,approve_date,business_line
                " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                " %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                " %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                " %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                " %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                " %s,%s"
                
                " )", (
                trace_id,k.create_time.strftime("%Y-%m-%d %H:%M:%S"),k.creator_department_id,k.creator_department_name,
                k.creator_id,
                k.creator_name,
                k.is_deleted,
                k.update_time,
                k.updator_department_id,
                k.updator_department_name,
                k.updator_id,
                k.updator_name,k.version,
                k.approve_date, k.business_line, k.contract_amount, k.contract_code, k.contract_info, k.contract_status,
                k.contract_type, k.erp_project, k.execute_end_date, k.execute_start_date, k.is_normal, k.is_onfile,
                k.is_relcontract,
                k.valid_date, k.final_channel_id, k.more_channel_id, k.channel_id, k.contract_modle_id,k.income_sure_type, k.income_type, k.pre_contract_id, k.new_contract_id, k.sys_date,
                k.sys_status, k.is_return, k.bz_id,
                # k.contract_division_code, k.contract_division_name,
                k.file_status,k.master_slave_contract, k.other_seal_status, k.person_charge, k.person_charge_domain_account,
                k.seal_status, k.submitter_domain_account, k.submitter_name,
                # k.final_channel_code, k.more_channel_code,k.channel_code,
                k.signed_company_code, k.final_channel_name, k.more_channel_name, k.channel_name,
                k.master_slave_contract_code, k.application_amount, k.remark,
                #k.person_charge_dept,k.person_charge_dept_id,k.contract_apply_id,
                k.seal_dept, k.seal_dept_id, k.seal_domain_account, k.seal_name,
                k.income_sure_type_code, k.income_type_code, k.weather_frameagree,
                # k.project_name,
                k.weather_settlement,k.relay_contract_code, k.relay_contract_id, k.master_slave_contract_id, k.final_channel_number,k.more_channel_number,
                k.channel_number,
                #k.has_import_tcData,k.start_time_tc,k.end_time_tc,k.import_tc_data_time,

        "模板名称"))

        now = datetime.now()
        str_now = now.strftime('%Y-%m-%d %H:%M:%S')
        #   新增表（yx_contract_apply）数据
        cur.execute("insert into yx_contract_apply( id,bz_id,create_time,creator_department_id,creator_department_name,creator_id,creator_name,is_deleted,update_time,updator_department_id,updator_department_name,updator_id,updator_name,version,apply_id,apply_incident_code,apply_incident_name,apply_status,apply_user_account,apply_user_department_id,apply_user_name,current_assignee_account,current_assignee_name,remark,trace_id,contract_amount,signed_company_code,flow_direction,frame_flag,master_slave_contract,rexpenditure_type,standard_flag ,apply_end_time,apply_start_time,process_definition_key,settle_flag) "
                    # ",apply_end_time,apply_start_time,process_definition_key,settle_flag) "
                    " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    (apply_id,k.bz_id,
                     k.create_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.create_time is None)   else '',
                     k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,
                     k.update_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.update_time is None)   else str_now,
                     k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,k.id,'','','',k.creator_id ,k.creator_department_id,k.creator_name,'','','',trace_id,
                     # k.contract_amount,
                     0,'','','','','','',str_now,str_now,'',''))

        #   新增表（yx_act_ext_todo_task ）数据
        # 获取审批状态
        cur.execute("select t.approval_status from yx_workflow t where t.busi_id = %s ",(k.id))
        # cur.execute("select t.approval_status from yx_workflow t where t.busi_id = %s ",('8a8085c25d798682015d8702e471076b'))
        result_status = cur.fetchone()

        cur.execute(
            "insert into yx_act_ext_todo_task( id,bz_id,create_time,creator_department_id,creator_department_name,creator_id,creator_name,is_deleted,update_time,updator_department_id,updator_department_name,updator_id,updator_name,version,business_id,task_status) "
            # ",apply_end_time,apply_start_time,process_definition_key,settle_flag) "
            " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
            (todo_task_id,k.bz_id,k.create_time,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.update_time,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,apply_id,'02'))

        print( apply_id, k.bz_id,k.create_time,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.update_time,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,k.id ,'','','' )

        # 写进审批日志
        re_count3 = cur.execute("select * from yx_workflow t where t.busi_id = %s ", (k.id))
        if re_count3 > 0:
            for l in generate_namedtuple(cur):
                log_id = uuid.uuid1()
                log_id = str(log_id).replace("-", "")
                print l
                # 更新表审批日志字段
                cur.execute(
                    "update yx_act_approve_record set business_id='" + apply_id + "' where process_instance_id = '" + l.process_instance_id + "' ")
cur.close()
conn.commit()
conn.close()
# for r in result:
        # print r
        # 写入trace 表
        # print (r['bill_number'],r['bill_type'],r['checker'])
        # print (r['bill_number'])
        # 写入 apply表
